package com.app.base;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.stereotype.Repository;


@Repository("baseDao") //数据层
@SuppressWarnings("unchecked")
public class BaseDaoImpl implements BaseDao{
	
	@Resource(name="jdbcTemplate")
	private JdbcTemplate jdbcTemplate;
	private Log log = LogFactory.getLog(BaseDaoImpl.class);	
	
	public int getTableId(String table,String id){
		 String sql = "select max("+id+")+1 as id from "+table;
		 int num = getInt(sql, null, "id");
		 return num;
	 }

	
	/**********************************
	 * 从spring的DataSource获得普通链接
	 */
	public Connection getNativeConn() throws SQLException {
		Connection conn = null;
		conn = DataSourceUtils.getConnection(jdbcTemplate.getDataSource());
		//conn = jdbcTemplate.getNativeJdbcExtractor().getNativeConnection(conn);
		return conn;
	}
	
	public final DataSource getDataSource() {
		 return (this.jdbcTemplate != null ? this.jdbcTemplate.getDataSource() : null);
	 }
	 
	//释放连接；
    public void closeConnection(Connection con) {
        DataSourceUtils.releaseConnection(con, getDataSource());
    }
	
    //批量执行sql数组
	public void addBachSql(String[] sql){
		jdbcTemplate.batchUpdate(sql);
	}
	
	//用普通链接的方式批量自动提交执行sql
	public void bachUpdateSqlAutoCommit(List<String> sqllist){
		try {
			Connection conn = getNativeConn();
			conn.setAutoCommit(false);
			Statement st = conn.createStatement();
			for(int i=0;i<sqllist.size();i++){
				st.addBatch(sqllist.get(i));
			}
			st.executeBatch();
			conn.commit();
			conn.setAutoCommit(true);
			st.close();closeConnection(conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	//用普通链接的方式批量提交执行sql
	public void bachUpdateSql(List<String> sqllist){
		try {
			Connection conn = getNativeConn();
			Statement st = conn.createStatement();
			for(int i=0;i<sqllist.size();i++){
				st.addBatch(sqllist.get(i));
			}
			st.executeBatch();
			conn.commit();
			st.close();closeConnection(conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	//获得整数sql 带参数 用于获取数据库中的整数及分页总数
	public Integer getInt(String sql, Object[] param,String returnColumn){	
		Integer str = 0;
		List re = jdbcTemplate.queryForList(sql, param);
		if(re!=null && re.size()>0){
			Map m = (Map)re.get(0);
			if(m!=null && m.get(returnColumn)!=null){
				try {
					String st =(m.get(returnColumn)).toString();
					if(st!=null) {
						st = st.trim();
						str = Integer.parseInt(st);
					}
					
				} catch (Exception e) {
					log.error("getDouble转换异常--"+returnColumn);
					e.printStackTrace();
					str = 0;
				}
			}
		}
		return str;
	}
	
	//获取数据库中的某个字段 带参数 返回这个字段的字符串集合
	public List<String> getArrayString(String sql, Object[] param,String column){
		List re = jdbcTemplate.queryForList(sql, param);
		List  list = new ArrayList();
		if(re!=null && re.size()>0){
			for(int i=0;i<re.size();i++){
				Map m = (Map)re.get(i);
				list.add(m.get(column));
			}
		}
		return list;
	}
	
	//以普通jdbc的方式 获取数据库中的double类型 带参数 指定错误信息
	public double getJDBCDouble(String sql, Object[] param,String returnColumn,String error)throws Exception{
		Connection conn = getNativeConn();
		PreparedStatement st = null;
		ResultSet rs = null;
		double re = 0d;
		try {
			st = conn.prepareStatement(sql);
			for(int i=0;i<param.length;i++){
				st.setObject(i+1, param[i]);
			}
			rs = st.executeQuery();
			
			while(rs.next()){
				Object obj = rs.getObject(returnColumn);
				if(obj!=null) 
					re = Double.parseDouble(obj.toString());
			}
			rs.close(); st.close();
		} catch (Exception e) {
			log.error(returnColumn+"---"+e.getMessage());
			throw new Exception(error);
		}
		return re;
	}
	
	// 获取数据库中的double类型 带参数 指定错误信息
	public double getDouble(String sql, Object[] param,String returnColumn,String error) throws Exception{	
		Double str = 0d;
		try {
			List re = jdbcTemplate.queryForList(sql, param);
			if(re!=null && re.size()>0){
				Map m = (Map)re.get(0);
				if(m!=null && m.get(returnColumn)!=null){
					try {
						String st =(m.get(returnColumn)).toString();
						if(st!=null) {
							st = st.trim();
							str = Double.parseDouble(st);
						}
						
					} catch (Exception e) {
						log.error("getDouble转换异常--"+returnColumn);
						str = 0d;
					}
				}
			}
			return str;
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception(error);
		}
	}
	
	// 获取数据库中的double类型  带参数
	public double getDouble(String sql, Object[] param,String returnColumn){	
		Double str = 0d;
		List re = jdbcTemplate.queryForList(sql, param);
		if(re!=null && re.size()>0){
			Map m = (Map)re.get(0);
			if(m!=null && m.get(returnColumn)!=null){
				try {
					String st =(m.get(returnColumn)).toString();
					if(st!=null) {
						st = st.trim();
						str = Double.parseDouble(st);
					}
					
				} catch (Exception e) {
					log.error("getDouble转换异常--"+returnColumn);
					e.printStackTrace();
					str = 0d;
				}
			}
		}
		return str;
	}
	
	//获取数据库中的一条Map记录 带参数 指定错误信息
	public Map getObject(String sql, Object[] param,String error)throws Exception{
		Map obj = null;
		try {
			List re = jdbcTemplate.queryForList(sql, param);
			if(re!=null && re.size()>0){
				obj = (Map)re.get(0);
			}
			return obj;
		} catch (Exception e) {
			throw new Exception(error);
		}
	}
	
	//获取数据库中的一条Map记录 带参数
	public Map getObject(String sql, Object[] param){
		Map obj = null;
		List re = jdbcTemplate.queryForList(sql, param);
		if(re!=null && re.size()>0){
			obj = (Map)re.get(0);
		}
		return obj;
	}
	
	// 获取数据库中的int类型 带参数 指定错误信息
	public Integer getInteger(String sql, Object[] param,String error) throws Exception{
		Integer str = 0;
		try {
			str = jdbcTemplate.queryForInt(sql, param);
			return str;
		} catch (Exception e) {
			throw new Exception(error);
		}
	}
	
	// 获取数据库中的int类型 带参数 
	public Integer getInteger(String sql, Object[] param){
		Integer str = 0;
		try {
			str = jdbcTemplate.queryForInt(sql, param);
		} catch (DataAccessException e) {
			log.error(e.getMessage());
		}
		return str;
	}
	
	// 获取数据库中的String类型 带参数 指定错误信息
	public String getString(String sql, Object[] param,String returnColumn,String error) throws Exception{
		try {
			String str = "";
			List re = jdbcTemplate.queryForList(sql, param);
			if(re!=null && re.size()>0){
				Map m = (Map)re.get(0);
				if(m!=null && m.get(returnColumn)!=null){
					try {
						str = m.get(returnColumn).toString();
					} catch (Exception e) {
						log.error("getString转换异常"+returnColumn);
						str = "";
					}
				}
			}
			return str;
		} catch (Exception e) {
			throw new Exception(error);
		}
	}
	
	// 获取数据库中的String类型 带参数
	public String getString(String sql, Object[] param,String returnColumn){
			String str = "";
			List re = jdbcTemplate.queryForList(sql, param);
			if(re!=null && re.size()>0){
				Map m = (Map)re.get(0);
				if(m!=null && m.get(returnColumn)!=null){
					try {
						str = m.get(returnColumn).toString();
					} catch (Exception e) {
						log.error("getString转换异常"+returnColumn);
						str = "";
					}
				}
			}
			return str;
	}
	
	// 获取数据库中的Map集合 带参数 指定错误信息
	public List getList(String sql, Object[] param,String error) throws Exception{
		try {
			List re = jdbcTemplate.queryForList(sql, param);
			return re;
		} catch (Exception e) {
			throw new Exception(error);
		}
	}
	
	// 获取数据库中的Map集合 带参数
	public List getList(String sql, Object[] param){
			List re = jdbcTemplate.queryForList(sql, param);
			return re;
	}
	
	//执行任意一条sql不带参数
	public void executeSQL(String sql){
		jdbcTemplate.execute(sql);
	}
	
	//执行任意一条sql不带参数 指定错误信息
	public void executeSQL(String sql,String error) throws Exception{
		try {
			jdbcTemplate.execute(sql);
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception(error);
		}
	}
	
	//执行任意一条sql 带参数 指定错误信息
	public int modifyObject(String sql, Object[] param,String error) throws Exception {
		int num = 1;
		try {
			jdbcTemplate.update(sql,param);
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception(error);
		}
		return num;
	}
	
	//执行任意一条sql 带参数
	public int modifyObject(String sql, Object[] param){
		int num = 1;
		try {
			jdbcTemplate.update(sql,param);
		} catch (RuntimeException e) {
			num = 0;
			e.printStackTrace();
		}
		return num;
	}
	
	//获取sql的指定字段字符串 用,链接加'   如： 'a','b','c'
	public String getStringArray(String sql, Object[] param,String returnColumn){
		String str = "null";
		List re = jdbcTemplate.queryForList(sql, param);
		if(re!=null && re.size()>0){
			str = "";
			for(int i=0;i<re.size();i++){
				Map m = (Map)re.get(i);
				if(m!=null && m.get(returnColumn)!=null){
					try {
						str += "'"+(String)m.get(returnColumn)+"',";
					} catch (Exception e) {
						log.error("getString转换异常"+returnColumn);
						str = "";
					}
				}
			}
			if(str.length()>0) str = str.substring(0, str.length()-1);
		}
		if("".equals(str)) str = "0";
		return str;
	}
	
	//获取sql的指定字段字符串 用,链接加\"   如： \"a\",\"b\",\"c\"
	public String getStringArray2(String sql, Object[] param,String returnColumn){
		String str = "null";
		List re = jdbcTemplate.queryForList(sql, param);
		if(re!=null && re.size()>0){
			str = "";
			for(int i=0;i<re.size();i++){
				Map m = (Map)re.get(i);
				if(m!=null && m.get(returnColumn)!=null){
					try {
						str += "\""+(String)m.get(returnColumn)+"\",";
					} catch (Exception e) {
						log.error("getString转换异常"+returnColumn);
						str = "";
					}
				}
			}
			if(str.length()>0) str = str.substring(0, str.length()-1);
		}
		if("".equals(str)) str = "0";
		return str;
	}
	
	//获取sql的指定整数字段字符串 用,链接   如： 1,2,3,4
	public String getIntArray(String sql, Object[] param,String returnColumn){
		String str = "null";
		List re = jdbcTemplate.queryForList(sql, param);
		if(re!=null && re.size()>0){
			str = "";
			for(int i=0;i<re.size();i++){
				Map m = (Map)re.get(i);
				if(m!=null && m.get(returnColumn)!=null){
					try {
						str += (Integer)m.get(returnColumn)+",";
					} catch (Exception e) {
						log.error("getString转换异常"+returnColumn);
						str = "";
					}
				}
			}
			if(str.length()>0) str = str.substring(0, str.length()-1);
		}
		return str;
	}

}
